package com.cennavi.minemap.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cennavi.minemap.bean.UploadBean;
import com.cennavi.minemap.util.DateTimeUtil;
import com.cennavi.minemap.util.GeoUtil;
import com.vividsolutions.jts.geom.Geometry;

@Component
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class UploadDao2 extends AbstractDao {
	public int createUploadDetail(Integer userId, Integer uploadId) {
		jdbcTemplate.setDataSource(uploadDataSource);
		StringBuffer sb = new StringBuffer("");
		sb.append("CREATE TABLE IF NOT EXISTS mm_upload_detail_" + userId + "_" + uploadId + " (");
		sb.append("id SERIAL not null,");
		sb.append("utime VARCHAR(20),");
		sb.append("attr jsonb DEFAULT '{}',");
		sb.append("geom geometry,");
		sb.append("constraint pk_upload_detail_" + userId + "_" + uploadId + " primary key (id)");
		sb.append(");");
		sb.append("CREATE INDEX IF NOT EXISTS idx_space_" + userId + "_" + uploadId + " ON mm_upload_detail_" + userId
				+ "_" + uploadId + " USING gist (geom);");

		try {
			jdbcTemplate.update(sb.toString());
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return 1;
	}

	public boolean addUploadDetail(List<Map<String, Object>> list, Integer userId, Integer uploadId) {
		try {
			jdbcTemplate.setDataSource(uploadDataSource);
			// createUploadDetail(userId, uploadId);
			String sql = "INSERT INTO MM_UPLOAD_DETAIL_" + userId + "_" + uploadId + " (utime, attr, geom) "
					+ "VALUES (?, ?::jsonb, st_geomfromtext(?, 4326))";
			jdbcTemplate.batchUpdate(sql, new AddDetailBatchPreparedStatementSetter(list));
			return true;
		} catch (Exception e) {
			return false;
		}

	}

	public String[] createUpdateSql(JSONArray array, Integer userId, Integer uploadId) {
		String[] reArray = new String[array.size()];
		for (int i = 0; i < array.size(); i++) {
			JSONObject json = array.getJSONObject(i);

			JSONArray del = json.getJSONArray("del");
			StringBuffer delStr = new StringBuffer("");
			if (del != null) {
				for (int j = 0; j < del.size(); j++) {
					delStr.append("-'" + del.getString(j) + "'");
				}
			}

			JSONObject attr = json.getJSONObject("attr");
			StringBuffer attrStr = new StringBuffer("");
			if (attr != null) {
				attrStr.append("||'" + attr.toJSONString() + "'::jsonb");
			}

			JSONObject geometry = json.getJSONObject("geom");
			StringBuffer geomStr = new StringBuffer("");
			if (geometry != null) {
				Geometry geom = null;
				try {
					geom = GeoUtil.readGeojson(geometry.toJSONString());
					geomStr.append("st_geomfromtext('" + GeoUtil.geometry2WKT(geom) + "', 4326)");
				} catch (Exception e) {
					e.printStackTrace();
				}
			}

			StringBuffer sql = new StringBuffer("UPDATE MM_UPLOAD_DETAIL_" + userId + "_" + uploadId + " SET ");
			if (!delStr.toString().equals("") || !attrStr.toString().equals("")) {
				sql.append("attr = attr" + delStr.toString() + attrStr.toString() + ",");
			}
			if (!geomStr.toString().equals("")) {
				sql.append("geom = " + geomStr + ",");
			}
			sql.deleteCharAt(sql.length() - 1).append(" WHERE id = " + json.getIntValue("id"));
			reArray[i] = sql.toString();
		}
		return reArray;
	}

	public boolean modUploadDetail(JSONArray array, Integer userId, Integer uploadId) {
		try {
			String[] sql = createUpdateSql(array, userId, uploadId);
			jdbcTemplate.setDataSource(uploadDataSource);
			jdbcTemplate.batchUpdate(sql);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public boolean delUploadDetail(JSONArray array, Integer userId, Integer uploadId) {
		try {
			jdbcTemplate.setDataSource(uploadDataSource);
			NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
			String sql = "DELETE FROM MM_UPLOAD_DETAIL_" + userId + "_" + uploadId + " WHERE ID IN (:id)";
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			parameters.addValue("id", Arrays.asList(array.toArray()));
			namedParameterJdbcTemplate.update(sql, parameters);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public List<JSONObject> queryUploadDetailList(Integer userId, Integer uploadId) {
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "SELECT id, utime, attr->>'name' as name, ST_AsGeoJson(geom) as geom " + "FROM mm_upload_detail_"
				+ userId + "_" + uploadId;
		return (List<JSONObject>) jdbcTemplate.query(sql, new UploadDetailMapper());
	}

	public List<JSONObject> queryUploadDetailById(Integer userId, Integer uploadId, Integer id) {
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "SELECT id, utime, attr, ST_AsGeoJson(geom) as geom " + "FROM mm_upload_detail_" + userId + "_"
				+ uploadId + " WHERE id = ?";
		return (List<JSONObject>) jdbcTemplate.query(sql, new Object[] { id }, new UploadDetailMapper());
	}

	public int insert(final UploadBean uploadBean) {
		jdbcTemplate.setDataSource(uploadDataSource);
		final String sql = "INSERT INTO MM_UPLOAD (name, userid, type, status, uploadTime, fileSize, describe) "
				+ "VALUES (?, ?, ?, 0, ?, ?, ?);";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, new String[] { "id" });
				ps.setString(1, uploadBean.getName());
				ps.setInt(2, uploadBean.getUserId());
				ps.setInt(3, uploadBean.getType());
				ps.setString(4, uploadBean.getUploadTime());
				ps.setLong(5, uploadBean.getFileSize());
				ps.setString(6, uploadBean.getDescribe());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();

	}

	public int update(final UploadBean uploadBean) {
		jdbcTemplate.setDataSource(uploadDataSource);
		String setSql = super.getSetSql(uploadBean);
		String sql = "UPDATE MM_UPLOAD SET " + setSql + " WHERE id = ?";
		return jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, uploadBean.getId());
			}
		});
	}

	public UploadBean queryById(int id) {
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "SELECT id, name, path, userId, status, type, uploadTime, "
				+ "fileSize, uploadSize, fileLine, succLine, failLine, describe FROM MM_UPLOAD WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new UploadMapper());
	}

	public List<UploadBean> queryByUserId(int userId) {
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "SELECT id, name, path, userId, status, type, uploadTime, "
				+ "fileSize, uploadSize, fileLine, succLine, failLine, describe FROM MM_UPLOAD WHERE userId = ?";
		return (List<UploadBean>) jdbcTemplate.query(sql, new Object[] { userId }, new UploadMapper());
	}

	protected class UploadMapper implements RowMapper<UploadBean> {
		public UploadBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			UploadBean upload = new UploadBean();
			upload.setId(rs.getInt("id"));
			upload.setName(rs.getString("name"));
			upload.setPath(rs.getString("path"));
			upload.setUserId(rs.getInt("userId"));
			upload.setStatus(rs.getInt("status"));
			upload.setType(rs.getInt("type"));
			upload.setUploadTime(rs.getString("uploadTime"));
			upload.setFileSize(rs.getLong("fileSize"));
			upload.setUploadSize(rs.getLong("uploadSize"));
			upload.setFileLine(rs.getInt("fileLine"));
			upload.setSuccLine(rs.getInt("succLine"));
			upload.setFailLine(rs.getInt("failLine"));
			upload.setDescribe(rs.getString("describe"));
			return upload;
		}
	}

	protected class UploadDetailMapper implements RowMapper<JSONObject> {
		public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
			JSONObject uploadDetail = new JSONObject();
			uploadDetail.put("id", rs.getInt("id"));

			if (isExistColumn(rs, "name")) {
				JSONObject properties = new JSONObject();
				properties.put("name", rs.getString("name"));
				uploadDetail.put("properties", properties);

			}
			if (isExistColumn(rs, "attr")) {
				uploadDetail.put("properties", JSONObject.parse(rs.getString("attr")));
			}
			uploadDetail.put("geometry", JSONObject.parse(rs.getString("geom")));
			return uploadDetail;
		}

		public boolean isExistColumn(ResultSet rs, String columnName) {
			try {
				if (rs.findColumn(columnName) > 0) {
					return true;
				}
			} catch (SQLException e) {
				return false;
			}
			return false;
		}
	}

	protected class UploadDetailOneMapper implements RowMapper<JSONObject> {
		public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
			JSONObject uploadDetail = new JSONObject();
			uploadDetail.put("id", rs.getInt("id"));
			uploadDetail.put("properties", rs.getInt("attr"));
			uploadDetail.put("geometry", rs.getString("geom"));
			return uploadDetail;
		}
	}

	protected class AddDetailBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
		final List<Map<String, Object>> list;

		/** 通过构造函数把要插入的数据传递进来处理 */
		public AddDetailBatchPreparedStatementSetter(List<Map<String, Object>> list) {
			this.list = list;
		}

		public int getBatchSize() {
			return list.size();
		}

		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Map<String, Object> map = (HashMap<String, Object>) list.get(i);
			ps.setString(1, DateTimeUtil.getNow());
			ps.setString(2, (String) map.get("attr"));
			ps.setString(3, (String) map.get("geom"));
		}
	}

	protected class UpdateDetailBatchPreparedStatementSetter implements BatchPreparedStatementSetter {
		final List<Map<String, Object>> list;

		/** 通过构造函数把要插入的数据传递进来处理 */
		public UpdateDetailBatchPreparedStatementSetter(List<Map<String, Object>> list) {
			this.list = list;
		}

		public int getBatchSize() {
			return list.size();
		}

		public void setValues(PreparedStatement ps, int i) throws SQLException {
			Map<String, Object> map = (HashMap<String, Object>) list.get(i);
			ps.setString(1, DateTimeUtil.getNow());
			ps.setString(2, (String) map.get("attr"));
			ps.setString(3, (String) map.get("geom"));
		}
	}
	
	public Integer querySoluId(Integer uploadId){
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "SELECT soluid FROM MM_UPLOAD WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { uploadId }, Integer.class);
	}
	
	public Integer saveSoluId(final Integer uploadId, final Integer soluId){
		jdbcTemplate.setDataSource(uploadDataSource);
		String sql = "UPDATE MM_UPLOAD SET soluid = ? WHERE id = ?";
		return jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, soluId);
				ps.setInt(2, uploadId);
			}
		});
	}
}
